﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using UniversityManagementSystem.DAL;

namespace UniversityManagementSystem.Gateway 
{
    [Serializable]
    public class CourseGateway : GatewayConnection
    {
        private string commandString = null;
        [NonSerialized]
        private SqlDataReader getData = null;
        public bool SaveCourse(Course aCourse)
        {
            bool saveCourse = false;
            int saved = 0;
            try
            {
                GetSqlConn.Open();
                commandString = string.Format("INSERT INTO t_Course(course_title,course_name,course_credit,course_description,course_department,course_semester)VALUES(@courseTitle,@courseName,@courseCredit,@courseDescription,@assignedDepartment,@assignedSemester)");
                GetSqlCommand.CommandText = commandString;
                GetSqlCommand.Parameters.Clear();
                GetSqlCommand.Parameters.Add("@courseTitle", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@courseTitle"].Value = aCourse.CourseTitle;
                GetSqlCommand.Parameters.Add("@courseName", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@courseName"].Value = aCourse.CourseName;
                GetSqlCommand.Parameters.Add("@courseCredit", SqlDbType.Float);
                GetSqlCommand.Parameters["@courseCredit"].Value = aCourse.CourseCredit;
                GetSqlCommand.Parameters.Add("@courseDescription", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@courseDescription"].Value = aCourse.CourseDescription;
                GetSqlCommand.Parameters.Add("@assignedDepartment", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@assignedDepartment"].Value = aCourse.CourseDepartment;
                GetSqlCommand.Parameters.Add("@assignedSemester", SqlDbType.Int);
                GetSqlCommand.Parameters["@assignedSemester"].Value = aCourse.CouresSemester;
                saved = GetSqlCommand.ExecuteNonQuery();
                if (saved > 0)
                {
                    saveCourse = true;
                }
            }
            catch (Exception exception)
            {
                throw new Exception("Error occured", exception);
            }
            finally
            {
                if ((GetSqlConn != null) && (GetSqlConn.State == ConnectionState.Open))
                    GetSqlConn.Close();
            }
            return saveCourse;
        }

        public bool CheckCourse(Course aCourse)
        {
            bool checkDone = false;
            try
            {
                GetSqlConn.Open();
                commandString = string.Format("SELECT * FROM t_Course WHERE course_title = @courseTitle OR course_name = @courseName");
                GetSqlCommand.CommandText = commandString;
                GetSqlCommand.Parameters.Clear();
                GetSqlCommand.Parameters.Add("@courseTitle", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@courseTitle"].Value = aCourse.CourseTitle;
                GetSqlCommand.Parameters.Add("@courseName", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@courseName"].Value = aCourse.CourseName;
                getData = GetSqlCommand.ExecuteReader();
                if (getData.Read())
                {
                    checkDone = true;
                }
                GetSqlConn.Close();
            }

            catch (Exception exception)
            {
                throw exception;
            }
            finally
            {
                if ((GetSqlConn != null) && (GetSqlConn.State == ConnectionState.Open))
                    GetSqlConn.Close();
            }
            return checkDone;
        }

        public List<Course> GetCourse(string department)
        {
            List<Course> getCourse = new List<Course>();
            try
            {
                GetSqlConn.Open();
                commandString = string.Format("SELECT * FROM t_Course WHERE course_department = @department");
                GetSqlCommand.CommandText = commandString;
                GetSqlCommand.Parameters.Clear();
                GetSqlCommand.Parameters.Add("@department", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@department"].Value = department;
                getData = GetSqlCommand.ExecuteReader();
                while (getData.Read())
                {
                    Course aCourse = new Course();
                    aCourse.CourseTitle = getData["course_title"].ToString();
                    aCourse.CourseName = getData["course_name"].ToString();
                    aCourse.CourseCredit = Convert.ToDouble(getData["course_credit"]);
                    getCourse.Add(aCourse);
                }
                GetSqlConn.Close();
            }

            catch (Exception exception)
            {
                throw exception;
            }
            finally
            {
                if ((GetSqlConn != null) && (GetSqlConn.State == ConnectionState.Open))
                    GetSqlConn.Close();
            }
            return getCourse;
        }

        public bool AssignCourse(Teacher aTeacher, Course aCourse)
        {
            bool assignCourseDone = false;
            int assigned = 0;
            int update = 0;
            try
            {
                GetSqlConn.Open();
                commandString = string.Format("INSERT INTO t_Course_schedule(course_title,assigned_teacher,schedeuled) VALUES(@courseTitle,@teacherId,@Y)");
                GetSqlCommand.CommandText = commandString;
                GetSqlCommand.Parameters.Clear();
                GetSqlCommand.Parameters.Add("@teacherId", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@teacherId"].Value = aTeacher.TeacherId;
                GetSqlCommand.Parameters.Add("@courseTitle", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@courseTitle"].Value = aCourse.CourseTitle;
                GetSqlCommand.Parameters.Add("@Y", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@Y"].Value = "Y";
                assigned = GetSqlCommand.ExecuteNonQuery();
                GetSqlConn.Close();
                if ((assigned > 0) && (this.UpdateTeacherCredit(aTeacher)))
                {
                    assignCourseDone = true;
                }

            }
            catch (Exception exception)
            {
                
                throw exception;
            }
            finally
            {
                if ((GetSqlConn != null) && (GetSqlConn.State == ConnectionState.Open))
                    GetSqlConn.Close();
            }
            return assignCourseDone;
        }

        private bool UpdateTeacherCredit(Teacher aTeacher)
        {
            bool updateTeacherCredit = false;
            int updateCredit = 0;
            try
            {
                GetSqlConn.Open();
                commandString = string.Format("UPDATE t_Teacher_academic SET remaining_credit = @remainingCredit,credit_overflow = @overflow WHERE teacher_id = @teacherId");
                GetSqlCommand.CommandText = commandString;
                GetSqlCommand.Parameters.Clear();
                GetSqlCommand.Parameters.Add("@remainingCredit", SqlDbType.Float);
                GetSqlCommand.Parameters["@remainingCredit"].Value = aTeacher.TeacherRemainingCredit;
                GetSqlCommand.Parameters.Add("@teacherId", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@teacherId"].Value = aTeacher.TeacherId;
                GetSqlCommand.Parameters.Add("@overflow", SqlDbType.Float);
                GetSqlCommand.Parameters["@overflow"].Value = aTeacher.TeacherCreditOverflow;
                updateCredit = GetSqlCommand.ExecuteNonQuery();
                GetSqlConn.Close();
                if (updateCredit > 0)
                {
                    updateTeacherCredit = true;
                }

            }
            catch (Exception exception)
            {

                throw exception;
            }
            finally
            {
                if ((GetSqlConn != null) && (GetSqlConn.State == ConnectionState.Open))
                    GetSqlConn.Close();
            }
            return updateTeacherCredit;
        }

        public string CheckAssignOrNot(Course aCourse)
        {
            string courseTeacher = "";
            try
            {
                GetSqlConn.Open();
                commandString =
                    "SELECT teacher_name FROM t_Teacher WHERE teacher_id IN (SELECT assigned_teacher FROM t_Course_schedule WHERE course_title = @courseTitle)";
                GetSqlCommand.CommandText = commandString;
                GetSqlCommand.Parameters.Clear();
                GetSqlCommand.Parameters.Add("@courseTitle", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@courseTitle"].Value = aCourse.CourseTitle;
                getData = GetSqlCommand.ExecuteReader();
                while (getData.Read())
                {
                    courseTeacher = getData["teacher_name"].ToString();
                }
                GetSqlConn.Close();
            }

            catch (Exception exception)
            {

                throw exception;
            }
            finally
            {
                if ((GetSqlConn != null) && (GetSqlConn.State == ConnectionState.Open))
                    GetSqlConn.Close();
            }
            return courseTeacher;
        }
        public List<Course> GelAllCourseBySemesterView(string department, string semester)
        {
            List<Course> courseBySemester = new List<Course>();
            try
            {
                GetSqlConn.Open();
                commandString = string.Format("SELECT * FROM t_Course WHERE course_department = @department AND course_semester = @semester");
                GetSqlCommand.CommandText = commandString;
                GetSqlCommand.Parameters.Clear();
                GetSqlCommand.Parameters.Add("@department", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@department"].Value = department;
                GetSqlCommand.Parameters.Add("@semester", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@semester"].Value = semester;
                getData = GetSqlCommand.ExecuteReader();
                while (getData.Read())
                {
                    Course aCourse = new Course();
                    aCourse.CourseTitle = getData["course_title"].ToString();
                    aCourse.CourseName = getData["course_name"].ToString();
                    aCourse.CourseCredit = Convert.ToDouble(getData["course_credit"]);
                    aCourse.CouresSemester = Convert.ToInt32(getData["course_semester"]);
                    aCourse.CourseTeacher = getData["assigned_teacher"].ToString();
                    courseBySemester.Add(aCourse);
                }
                GetSqlConn.Close();
            }

            catch (Exception exception)
            {

                throw exception;
            }
            finally
            {
                if ((GetSqlConn != null) && (GetSqlConn.State == ConnectionState.Open))
                    GetSqlConn.Close();
            }
            return courseBySemester;
        }
        public List<Course> GelAllCourseBySemester(string department, string semester)
        {
            List<Course> courseBySemester = new List<Course>();
            try
            {
                GetSqlConn.Open();
                commandString = string.Format("SELECT * FROM t_Course,t_Course_schedule WHERE t_Course.course_department = @department AND t_Course.course_semester = @semester AND t_Course.course_title IN (SELECT course_title FROM t_Course_schedule) AND t_Course_schedule.course_title IN (SELECT course_title FROM t_Course WHERE course_department = @department AND course_semester = @semester AND course_title IN (SELECT course_title FROM t_Course_schedule))");
                GetSqlCommand.CommandText = commandString;
                GetSqlCommand.Parameters.Clear();
                GetSqlCommand.Parameters.Add("@department", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@department"].Value = department;
                GetSqlCommand.Parameters.Add("@semester", SqlDbType.Int);
                GetSqlCommand.Parameters["@semester"].Value = semester;
                getData = GetSqlCommand.ExecuteReader();
                while (getData.Read())
                {
                    Course aCourse = new Course();
                    aCourse.CourseTitle = getData["course_title"].ToString();
                    aCourse.CourseName = getData["course_name"].ToString();
                    aCourse.CourseCredit = Convert.ToDouble(getData["course_credit"]);
                    aCourse.CouresSemester = Convert.ToInt32(getData["course_semester"]);
                    aCourse.CourseTeacher = getData["assigned_teacher"].ToString();
                    courseBySemester.Add(aCourse);
                }
                GetSqlConn.Close();
            }

            catch (Exception exception)
            {

                throw exception;
            }
            finally
            {
                if ((GetSqlConn != null) && (GetSqlConn.State == ConnectionState.Open))
                    GetSqlConn.Close();
            }
            return courseBySemester;
        }

        public List<Course> ShowUnassignedCourses(string department, string semester)
        {
            List<Course> unassignedCourses = new List<Course>();
            try
            {
                GetSqlConn.Open();
                commandString = string.Format("SELECT * FROM t_Course WHERE course_department = @department AND course_semester = @semester AND assigned_teacher = @unassigned");
                GetSqlCommand.CommandText = commandString;
                GetSqlCommand.Parameters.Clear();
                GetSqlCommand.Parameters.Add("@department", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@department"].Value = department;
                GetSqlCommand.Parameters.Add("@semester", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@semester"].Value = semester;
                GetSqlCommand.Parameters.Add("@unassigned", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@unassigned"].Value = "unassigned";
                getData = GetSqlCommand.ExecuteReader();
                while (getData.Read())
                {
                    Course aCourse = new Course();
                    aCourse.CourseTitle = getData["course_title"].ToString();
                    aCourse.CourseName = getData["course_name"].ToString();
                    aCourse.CourseCredit = Convert.ToDouble(getData["course_credit"]);
                    aCourse.CourseDescription = getData["course_description"].ToString();
                    unassignedCourses.Add(aCourse);
                }
                GetSqlConn.Close();
            }
            catch (Exception exception)
            {
                
                throw exception;
            }
            finally
            {
                if ((GetSqlConn != null) && (GetSqlConn.State == ConnectionState.Open))
                    GetSqlConn.Close();
            }
            return unassignedCourses;
        }

        public List<Course> ShowUnschedeuledCourses(string department, string semester)
        {
            List<Course> unschedeuledCourses = new List<Course>();
            try
            {
                GetSqlConn.Open();
                commandString = string.Format("SELECT * FROM t_Course WHERE course_department = @department AND course_semester = @semester AND schedeuled = @schedeuled");
                GetSqlCommand.CommandText = commandString;
                GetSqlCommand.Parameters.Clear();
                GetSqlCommand.Parameters.Add("@department", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@department"].Value = department;
                GetSqlCommand.Parameters.Add("@semester", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@semester"].Value = semester;
                GetSqlCommand.Parameters.Add("@schedeuled", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@schedeuled"].Value = "unschedeuled";
                getData = GetSqlCommand.ExecuteReader();
                while (getData.Read())
                {
                    Course aCourse = new Course();
                    aCourse.CourseTitle = getData["course_title"].ToString();
                    aCourse.CourseName = getData["course_name"].ToString();
                    aCourse.CourseCredit = Convert.ToDouble(getData["course_credit"]);
                    aCourse.CourseDescription = getData["course_description"].ToString();
                    unschedeuledCourses.Add(aCourse);
                }
                GetSqlConn.Close();
            }
            catch (Exception exception)
            {

                throw exception;
            }
            finally
            {
                if ((GetSqlConn != null) && (GetSqlConn.State == ConnectionState.Open))
                    GetSqlConn.Close();
            }
            return unschedeuledCourses;
        }

        public List<Course> GetAllCourses()
        {
            List<Course> getAllCourse = new List<Course>();
            try
            {
                GetSqlConn.Open();
                commandString = string.Format("SELECT * FROM t_Course");
                GetSqlCommand.CommandText = commandString;
                getData = GetSqlCommand.ExecuteReader();
                while (getData.Read())
                {
                    Course aCourse = new Course();
                    aCourse.CourseTitle = getData["course_title"].ToString();
                    aCourse.CourseName = getData["course_name"].ToString();
                    aCourse.CourseCredit = Convert.ToDouble(getData["course_credit"]);
                    aCourse.CourseDepartment = getData["course_department"].ToString();
                    aCourse.CouresSemester = Convert.ToInt32(getData["course_semester"]);
                    aCourse.CourseDescription = getData["course_description"].ToString();
                    getAllCourse.Add(aCourse);
                }
                GetSqlConn.Close();
            }

            catch (Exception exception)
            {

                throw exception;
            }
            finally
            {
                if ((GetSqlConn != null) && (GetSqlConn.State == ConnectionState.Open))
                    GetSqlConn.Close();
            }
            return getAllCourse;
        }

        public List<Course> GetAllCoursesByDepartment(string department)
        {
            List<Course> allCoursesByDepartment = new List<Course>();
            try
            {
                GetSqlConn.Open();
                commandString = string.Format("SELECT * FROM t_Course WHERE course_department = @department");
                GetSqlCommand.CommandText = commandString;
                GetSqlCommand.Parameters.Clear();
                GetSqlCommand.Parameters.Add("@department", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@department"].Value = department;
                getData = GetSqlCommand.ExecuteReader();
                while (getData.Read())
                {
                    Course aCourse = new Course();
                    aCourse.CourseTitle = getData["course_title"].ToString();
                    aCourse.CourseName = getData["course_name"].ToString();
                    aCourse.CourseCredit = Convert.ToDouble(getData["course_credit"]);
                    aCourse.CouresSemester = Convert.ToInt32(getData["course_semester"]);
                    aCourse.CourseTeacher = getData["assigned_teacher"].ToString();
                    allCoursesByDepartment.Add(aCourse);
                }
                GetSqlConn.Close();
            }

            catch (Exception exception)
            {

                throw exception;
            }
            finally
            {
                if ((GetSqlConn != null) && (GetSqlConn.State == ConnectionState.Open))
                    GetSqlConn.Close();
            }
            return allCoursesByDepartment;
        }

        public List<Course> GetAllEnrolledCourses(string registrationNo)
        {
            List<Course> allEnrolledCourses = new List<Course>();
            try
            {
                GetSqlConn.Open();
                commandString = "SELECT * FROM t_Course WHERE course_title IN (SELECT course_title FROM t_Enrolled_courses WHERE registration_no = @registrationNo)";
                GetSqlCommand.CommandText = commandString;
                GetSqlCommand.Parameters.Clear();
                GetSqlCommand.Parameters.Add("@registrationNo", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@registrationNo"].Value = registrationNo;
                getData = GetSqlCommand.ExecuteReader();
                while (getData.Read())
                {
                    Course aCourse = new Course();
                    aCourse.CourseTitle = getData["course_title"].ToString();
                    aCourse.CourseName = getData["course_name"].ToString();
                    allEnrolledCourses.Add(aCourse);
                }
                GetSqlConn.Close();
            }

            catch (Exception exception)
            {

                throw exception;
            }
            finally
            {
                if ((GetSqlConn != null) && (GetSqlConn.State == ConnectionState.Open))
                    GetSqlConn.Close();
            }
            return allEnrolledCourses;
        }
        public bool CheckCourseEnrolled(string registrationNo, string courseTitle)
        {
            bool hasCourse = false;
            try
            {
                GetSqlConn.Open();
                commandString = string.Format("SELECT * FROM t_Enrolled_courses WHERE registration_no = @regNo AND course_title = @courseTitle");
                GetSqlCommand.CommandText = commandString;
                GetSqlCommand.Parameters.Clear();
                GetSqlCommand.Parameters.Add("@regNo", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@regNo"].Value = registrationNo;
                GetSqlCommand.Parameters.Add("@courseTitle", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@courseTitle"].Value = courseTitle;
                getData = GetSqlCommand.ExecuteReader();
                if (getData.Read())
                {
                    hasCourse = true;
                }
                GetSqlConn.Close();
            }
            catch (Exception exception)
            {
                throw exception;
            }
            finally
            {
                if ((GetSqlConn != null) && (GetSqlConn.State == ConnectionState.Open))
                    GetSqlConn.Close();
            }
            return hasCourse;
        }

        public bool EnrollAcourse(string registrationNo, Course aCourse)
        {
            bool enrollDone = false;
            int saved = 0;
            try
            {
                GetSqlConn.Open();
                commandString = string.Format("INSERT INTO t_Enrolled_courses(registration_no,course_title)VALUES(@registrationNo,@courseTitle)");
                GetSqlCommand.CommandText = commandString;
                GetSqlCommand.Parameters.Clear();
                GetSqlCommand.Parameters.Add("@registrationNo", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@registrationNo"].Value = registrationNo;
                GetSqlCommand.Parameters.Add("@courseTitle", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@courseTitle"].Value = aCourse.CourseTitle;
                saved = GetSqlCommand.ExecuteNonQuery();
                if (saved > 0)
                {
                    enrollDone = true;
                }
                GetSqlConn.Close();
            }
            catch (Exception exception)
            {
                throw exception;
            }
            finally
            {
                if ((GetSqlConn != null) && (GetSqlConn.State == ConnectionState.Open))
                    GetSqlConn.Close();
            }
            return enrollDone;
        }

        public bool SaveResult(string registrationNo, string courseName, string gradePoint)
        {
            bool saveResultDone = false;
            int saved = 0;
            try
            {
                GetSqlConn.Open();
                commandString = string.Format("UPDATE t_Enrolled_courses SET obtained_grade_letter = @gradePoint WHERE registration_no = @registrationNo AND course_title = @courseName");
                GetSqlCommand.CommandText = commandString;
                GetSqlCommand.Parameters.Clear();
                GetSqlCommand.Parameters.Add("@gradePoint", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@gradePoint"].Value = gradePoint;
                GetSqlCommand.Parameters.Add("@registrationNo", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@registrationNo"].Value = registrationNo;
                GetSqlCommand.Parameters.Add("@courseName", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@courseName"].Value = courseName;
                saved = GetSqlCommand.ExecuteNonQuery();
                if (saved > 0)
                {
                    saveResultDone = true;
                }
                GetSqlConn.Close();
            }
            catch (Exception exception)
            {
                throw exception;
            }
            finally
            {
                if ((GetSqlConn != null) && (GetSqlConn.State == ConnectionState.Open))
                    GetSqlConn.Close();
            }
            return saveResultDone;
        }
    }
}